"""
Case Type   : mysql protocol compatibility
Case Name   : 协议兼容下验证多种数据类型建表
Create At   : 2024/10/23
Owner       : lvlintao666
Description :
    1、设置协议兼容相关配置
    2、建表
    3、验证多种类型数据插入
    4、清理环境
Expect      :
    1、配置成功
    2、建表成功
    3、查询成功
    4、清理成功
History     :
"""

import os
import time
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class IndeBlurredTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.com = Common()
        self.comsh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql5')
        self.db_name = "proto_test_db"
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.create_db = f"drop database if exists {self.db_name};" \
                         f"create database {self.db_name} dbcompatibility='B'"
        self.user = f"proto_test_17"
        self.password = f"proto_test123"
        self.create_u = f"drop user if exists {self.user} cascade;" \
                        f"create user {self.user} with password '{self.password}';" \
                        f"grant all privileges to {self.user};" \
                        f"select set_native_password('{self.user}','{self.password}','');"
        self.show_cmd = f"show enable_dolphin_proto;" \
                        f"show dolphin_server_port;"
        self.create_tb = f"drop table if exists tab_protocol_0018;" \
                         f"create table tab_protocol_0018(col11 tinyint,col12 smallint," \
                         f"col13 integer,col14 bigint,col15 float4,col16 float8," \
                         f"col17 decimal,col18 numeric,col19 bit(4),col21 char(3)," \
                         f"col22 varchar(20),col23 binary(5),col24 varbinary(20)," \
                         f"col25 tinyblob,col26 blob,col27 mediumblob,col28 longblob," \
                         f"col29 enum('521','51.0','5.0','974.1')," \
                         f"col210 set('1','11','100','887.11'),col211 text,col212 json," \
                         f"col31 date,col32 datetime,col33 timestamp,col34 time," \
                         f"col35 year,col36 datetime(6),col37 timestamp(6));"
        self.insert = f"""
            insert into tab_protocol_0018 values(7,30,8,10,8.516,10.247,553.6124,
            735.8111,B'1101','7.1','31.768','3.017','5347','961.1','999'::blob,'332.1',
            '3570','5.0','100','2','1.00'::json,'3587-01-01','5361-07-11 01:01:29','339-05-28',
            '23:00:00','2024','2001-07-15 02:05:33.4421','665-09-03 22:23:24.11111'),
            (52,19,21,112,97.31,40.227,918.989,537.251,B'1010','2.5','31.768','2.581','942.3698',
            '3321.475','2243'::blob,'3327','240578.1121','521','1','2','2.00'::json,'2020-06-15',
            '2020-05-31 17:22:24','2010-12-12','09:37:11','1996','2020-05-31 17:22:24.253164',
            '1995-03-26 05:15:15.71'),(11,31,27,105,9.271,345.374,3700.624,188.881,B'1110','0.1',
            '2.1587','32.47','53.12','221','707'::blob,'53.12','912.55','51.0','11','23.7','0.04'::json,
            '331-06-27','4417-05-06 14:31:09','100-01-02','23:59:59','2039','1772-11-16 09:22:36.447',
            '9654-03-29 23:10:11.886'),(80,119,211,312,97.831,0.227,0.9989,7.251,B'1011','0.9','53.42',
            '551.1','4.021','3.112','684'::blob,'3.27','10.211','974.1','887.11','31.13','25.71'::json,
            '220-03-15','2222-11-30 22:22:24','9999-12-31','23:59:59','1991','1-11-11 11:11:11.33689',
            '1998-06-26 07:15:36.1158');
        """
        self.query = f"select * from tab_protocol_0018;"
        self.flag1 = f""
        self.clean_tb = f"drop table if exists tab_protocol_0018 cascade constraints;" \
                        f"drop user if exists {self.user} cascade;"
        self.clean_db = f"drop database {self.db_name};"

    def test_index(self):
        text = '--step1:设置协议兼容相关配置;expect:创建成功--'
        logger.info(text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = on')
        logger.info(msg)
        my_port = int(self.pri_node.db_port) + 10
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = {my_port}')
        logger.info(msg)
        sed_cmd = f"sed -i '$a dolphin.default_database_name={self.db_name}' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        show_res = self.comsh.execut_db_sql(self.show_cmd)
        logger.info(show_res)
        self.assertIn('on', show_res, '执行失败' + text)
        self.assertIn(f'{my_port}', show_res, '执行失败' + text)

        logger.info(self.create_db)
        db_res = self.comsh.execut_db_sql(self.create_db)
        logger.info(db_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_res,
                      '执行失败' + text)
        logger.info(self.create_u)
        u_res = self.comsh.execut_db_sql(self.create_u, dbname=self.db_name)
        logger.info(u_res)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, u_res,
                      '执行失败' + text)

        text = '--step2:M*连接数据库多种数据类型建表;expect:创建成功--'
        logger.info(text)
        my_sh = CommonMySQL('mysql5')
        c_res = my_sh.execut_db_sql(self.create_tb, self.db_name, self.pri_node.db_host,
                                    my_port, self.user, self.password)
        logger.info(c_res)
        self.assertNotIn('ERROR', c_res, '执行失败' + text)

        text = '--step3:M*连接数据库查询数据;expect:成功--'
        logger.info(text)
        insert_res = my_sh.execut_db_sql(self.insert, self.db_name, self.pri_node.db_host,
                                    my_port, self.user, self.password)
        logger.info(insert_res)
        self.assertNotIn('ERROR', insert_res, '执行失败' + text)
        query_res = my_sh.execut_db_sql(self.query, self.db_name, self.pri_node.db_host,
                                    my_port, self.user, self.password)
        logger.info(query_res)
        self.assertNotIn('ERROR', query_res, '执行失败' + text)

    def tearDown(self):
        text = '--step4:清理环境;expect:成功--'
        logger.info(text)
        clean_res = self.comsh.execut_db_sql(self.clean_tb, dbname=self.db_name)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, clean_res,
                      '执行失败' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, clean_res,
                      '执行失败' + text)
        clean_res = self.comsh.execut_db_sql(self.clean_db)
        logger.info(clean_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, clean_res,
                      '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       'enable_dolphin_proto = off')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        msg = self.comsh.execute_gsguc('set',
                                       self.constant.GSGUC_SUCCESS_MSG,
                                       f'dolphin_server_port = 3308')
        logger.info(msg)
        self.assertTrue(msg, '执行失败' + text)
        sed_cmd = f"sed -i '/dolphin.default_database_name={self.db_name}/d' " \
                  f"{self.conf_path}"
        logger.info(sed_cmd)
        res = self.com.get_sh_result(self.pri_node, sed_cmd)
        logger.info(res)
        msg = self.comsh.restart_db_cluster()
        logger.info(msg)
        status = self.comsh.get_db_cluster_status()
        logger.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
